#coding:utf-8
import pymysql


import pandas as pd
import csv
import re
import numpy as np
from datetime import datetime

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL查询
#cursor.execute("create database dbtest charset=utf8;")
# 使用 fetchone() 方法获取单条数据


sql="""
    select s3.order1,s3.date_sh,s3.type_sh,s3.reason_sh , s3.Product_BianMa ,Product_price,date_tj   from 
    (
    select  order1,date_sh,type_sh,reason_sh ,Product_BianMa   from shouhou  
    where (order_sh="同意退款，退款成功" or  order_sh="同意退款，退款中" ) and month(date_sh)>9 )  s3
    left join  
    ( select order1, date_tj,Product_price, Product_BianMa  from doudian_day  where Product_price>0 and (order_sh = "退款成功"  or order_sh = "退款完成"  or order_sh = "售后完成"  or order_sh = "退款完成"  or order_sh = "已全额退款" or order_sh = "退货退款完成") and month(day)=10 ) d
    
    on (s3.order1=d.order1 and   s3.Product_BianMa=d.Product_BianMa)
    """
cursor.execute(sql)
result=cursor.fetchall()

xs=pd.DataFrame(list(result),columns=['订单号','售后时间','售后方式','售后原因','商家编码','价格','下单时间'])


xs['时间差'] = xs['售后时间'] - xs['下单时间']
xs["hour"] = xs.cha.map(lambda x: x.seconds/3600 )

xs.to_excel(r'G:\工作\2024年订单\10月\结果\售后时间10月.xlsx')